IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddDelegateMap]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddDelegateMap]
GO

SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************              
* Name:   [AddDelegateMap]              
* Purpose:  Sets the delegate to the supervisor
   And grants the delegate(s) access to COI if one does not exist.   
* NOte: The access will be granted only for users with fwkDomainUserId.              
* PARAMETERS(IN)              
* Name          Description                   
* -------------      -------------------------------------------              
* @xml       xml Document containing the hierarchy(REQUIRED)        
 The passed in XML has the following form:      
  <NewDataSet>      
   <Table>      
    <ParentId>234711</ParentId>   
 <ChildId>78543</ChildId>  
 <Removed>0</Removed>    
    </Table>      
   <Table>      
    <ParentId>185678</ParentId>   
 <ChildId>67543</ChildId>  
 <Removed>0</Removed>      
   </Table>      
   ...      
  </NewDataSet>    
                 
*********************************************************************/              
              
CREATE PROCEDURE [dbo].[AddDelegateMap] 
(              
@xml text         
)              
AS BEGIN              
SET NOCOUNT ON              
              
 declare @xmlDoc int                
 declare @admPersonIds table (Sequence int identity, [SupervisorId]int,[DelegateId]int,[Removed] bit)                
 exec sp_xml_preparedocument @xmlDoc output, @xml       
 insert into @admPersonIds select [SupervisorId],[DelegateId],[removed] from openxml(@xmlDoc, '//Table1',3) with ([supervisorId] int,[delegateId]int,[removed] bit)         
 exec sp_xml_removedocument @xmlDoc         
       
 Declare @supervisorId as integer      
 Declare @delegateId as integer      
      
 DECLARE delegateCursor CURSOR FOR      
 select [SupervisorId],[DelegateId] from @admPersonIds         
         
 OPEN delegateCursor      
      
 -- Perform the first fetch.      
 FETCH NEXT FROM delegateCursor into @supervisorId,@delegateId      
        
 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.      
 WHILE @@FETCH_STATUS = 0      
 BEGIN      
       
 if Exists(Select 1 from COI_Delegate_Map where SupervisorId=@supervisorId and DelegateId=@delegateId)      
   BEGIN      
  Update COI_Delegate_Map set Removed=0 where SupervisorId=@supervisorId and DelegateId=@delegateId      
   END      
 Else      
   BEGIN      
   Insert into COI_Delegate_Map values (@supervisorId,@delegateId,0)      
   END  
 -- Grant access, if the user doesn't have one.    
 Declare @fwkDomainUserId int    
 set @fwkDomainUserId =null  
  Select @fwkDomainUserId=FwkDomainUserID from AdmPerson where Id=@delegateId and AdmPersonTypeId=1     
  if (@fwkDomainUserId is not null)    
   BEGIN    
    Declare @moduleBitMask int  
    set @moduleBitMask =null  
    Select @moduleBitMask = ModuleBitMask from fwkApplicationuser where fwkDomainUserID=@fwkDomainUserId    
    if @moduleBitMask is null    
  INSERT INTO FwkApplicationUser(ApplicationBitmask,IsActiveBitmask,FwkDomainUserId,ModuleBitmask,ModuleRoleBitmask)      
  VALUES(4,4,@fwkDomainUserId,128,0)--Default values      
    else if (@moduleBitMask & 128 = 0)    
  BEGIN    
   Set @moduleBitMask=@moduleBitMask + 128    
   Update fwkApplicationuser set ModuleBitMask = @moduleBitMask where fwkDomainUserID=@fwkDomainUserId    
  END    
    END     
    -- This is executed as long as the previous fetch succeeds.      
    FETCH NEXT FROM delegateCursor into @supervisorId,@delegateId      
  END      
 CLOSE delegateCursor      
 DEALLOCATE delegateCursor      
END      

